import datetime
import json

import cx_Oracle
import pandas as pd

"""批量删除16张报表的job"""

user = "pigpos"
passwd = "P1g#2023pos"
# listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
table_set = {'1'}
path = "C:/Users/yangjianzhang/Desktop/"


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


if __name__ == '__main__':
    print('开始')
    org_code = '031522113'
    sql = "select JOB_NAME,LAST_START_DATE from user_scheduler_jobs where 1=1 and (job_name like 'OW_{}%' or job_name like 'W_{}%')" \
          "".format(org_code,org_code)
    field_list = select(sql)
    field_list = json.loads(field_list)
    str = "begin\n"
    for f in field_list:
        job_name = f['JOB_NAME']
        # str = str + "dbms_scheduler.stop_job('" + job_name + "');\n"
        str = str + "dbms_scheduler.drop_job('" + job_name + "');\n"

    str = str + "end;"
    print(str)
